{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pymysql\n",
    "# 连接数据库\n",
    "connect = pymysql.Connect(host=\"localhost\",port=\"3306\",user=\"root\",passwd=\"root\",db=\"football_latte\",charset=\"utf8\")\n",
    "# 获取游标\n",
    "cursor = connect.cursor()\n",
    "\n",
    "# 插入数据  \n",
    "sql = \"INSERT INTO money (name, account, saving) VALUES ( '%s', '%s', %.2f )\"  \n",
    "data = ('雷军', '13512345678', 10000)  \n",
    "cursor.execute(sql % data)  \n",
    "connect.commit()  \n",
    "print('成功插入', cursor.rowcount, '条数据')  \n",
    "  \n",
    "# 修改数据  \n",
    "sql = \"UPDATE money SET saving = %.2f WHERE account = '%s' \"  \n",
    "data = (8888, '13512345678')  \n",
    "cursor.execute(sql % data)  \n",
    "connect.commit()  \n",
    "print('成功修改', cursor.rowcount, '条数据')  \n",
    "  \n",
    "# 查询数据  \n",
    "sql = \"SELECT name,saving FROM money WHERE account = '%s' \"  \n",
    "data = ('13512345678',)  \n",
    "cursor.execute(sql % data)  \n",
    "for row in cursor.fetchall():  \n",
    "    print(\"Name:%s\\tSaving:%.2f\" % row)  \n",
    "print('共查找出', cursor.rowcount, '条数据')  \n",
    "  \n",
    "# 删除数据  \n",
    "sql = \"DELETE FROM money  WHERE account = '%s' LIMIT %d\"  \n",
    "data = ('13512345678', 1)  \n",
    "cursor.execute(sql % data)  \n",
    "connect.commit()  \n",
    "print('成功删除', cursor.rowcount, '条数据')  \n",
    "  \n",
    "# 事务处理  \n",
    "sql_1 = \"UPDATE money SET saving = saving + 1000 WHERE account = '18012345678' \"  \n",
    "sql_2 = \"UPDATE money SET expend = expend + 1000 WHERE account = '18012345678' \"  \n",
    "sql_3 = \"UPDATE money SET income = income + 2000 WHERE account = '18012345678' \"  \n",
    "  \n",
    "try:  \n",
    "    cursor.execute(sql_1)  # 储蓄增加1000  \n",
    "    cursor.execute(sql_2)  # 支出增加1000  \n",
    "    cursor.execute(sql_3)  # 收入增加2000  \n",
    "except Exception as e:  \n",
    "    connect.rollback()  # 事务回滚  \n",
    "    print('事务处理失败', e)  \n",
    "else:  \n",
    "    connect.commit()  # 事务提交  \n",
    "    print('事务处理成功', cursor.rowcount)  \n",
    "  \n",
    "# 关闭连接  \n",
    "cursor.close()  \n",
    "connect.close()  "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
